package com.saic.data.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import com.saic.data.table.TableFile;


/**
 * @DESC:ORACLE操作
 * @author Xinshiyou
 */
public class DBUtil {

	public static final String url = "jdbc:oracle:thin:@10.129.11.129:1521:ESSBASE";
	public static final String name = "oracle.jdbc.driver.OracleDriver";
	public static final String user = "SAIC_ETL";
	public static final String password = "SAICetl1";

	public Connection conn = null;

	public DBUtil() {
		init();
	}

	/** create table */
	public boolean createTable(String tableName, List<String> colName) {
		pm("SqlDBUtil-SQL table: " + tableName);
		StringBuffer sb = new StringBuffer();
		sb.append("create table " + tableName + "(");
		for (String s : colName)
			sb.append("" + s + " varchar(80),");
		String sql = sb.substring(0, sb.length() - 1);
		sql += ");";
		return execute(sql);
	}

	/** Create, Insert, Delete, Drop */
	public boolean execute(String sql) {
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			return ps.execute();
		} catch (SQLException e) {
			pm("SqlDBUtil-execute:"+e.getMessage());
			return false;
		} finally {
			if (null != ps)
				try {
					ps.close();
				} catch (SQLException e) {
					pm("SqlDBUtil-execute:"+e.getMessage());
				}
		}	
	}

	/** Create, Insert, Delete, Drop */
	public boolean update(String sql) {
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			return ps.executeUpdate() > 0;
		} catch (SQLException e) {
			pm("SqlDBUtil-update:"+e.getMessage());
			return false;
		} finally {
			if (null != ps)
				try {
					ps.close();
				} catch (SQLException e) {
					pm(this.getClass(),"SqlDBUtil-update:"+e.getMessage());
				}
		}
	}

	public int queryCount(String sql) {
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				int resSum = rs.getInt(1);
				return resSum;
			}else return 0;
						
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (null != ps)
				try {
					ps.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
		}
		return 0;
	}

	public boolean deleteByMonth(String tableName, String year,
			String month) {
		String sql = "delete from " + tableName + " where YEARS=" + "\'"
				+ year + "\' and PERIOD=\'" + month + "\'";
		return update(sql);
	}
	
	public boolean deleteMonthWithoutDate(String tableName,String sdate) {
		String year = sdate.substring(0, 4);
		String month = sdate.substring(4, 6);
		String sql = "delete from " + tableName + " where YEARS=" + "\'"
				+ year + "\' and PERIOD=\'" + month + "\' and CUSTOM14 <>\'"+sdate+ "\'";
		return update(sql);
	}

	/** Initialize */
	public void init() {
		try {
			Class.forName(name);
			conn = DriverManager.getConnection(url, user, password);
			if (null == conn)
				pm(this.getClass(),"Connect is NULL");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/** Close database */
	public void close() {
		try {
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	private void pm(String msg) {
		pm(this.getClass(), msg);
	}
	private void pm(Class<?> cls, String msg) {
		pm(cls.getName(), msg);
	}

	private void pm(String cls, String msg) {
		String data = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date());
		System.out.println("["+data+"] Print by " + cls + "-->" + msg);
	}
}
